﻿using Microsoft.Data.SqlClient;
using MyBBSWebApi.DAL.Core;
using MyBBSWebApi.Models;
using System;
using System.Collections.Generic;
using System.Data;

namespace MyBBSWebApi.DAL
{
    public class UserDal
    {

        public List<Users> GetAll()
        {
            DataTable res = SqlHelper.ExecuteTable("Select * from Users");
            List<Users> userList = ToModelList(res);
            return userList;
        }

        public List<Users> GetUserByUserNoAndPwd(string userNo, string password)
        {
            DataTable res = SqlHelper.ExecuteTable("select * from Users Where UserNo=@UserNo AND Password=@Password", new SqlParameter("@UserNo", userNo), new SqlParameter("@Password", password));
            List<Users> userList = ToModelList(res);

            return userList;
        }

        public Users GetUsersById(int ID)
        {
            DataRow row = null;
            DataTable res = SqlHelper.ExecuteTable("select * from Users Where Id=@Id", new SqlParameter("@Id", ID));
            if (res.Rows.Count > 0)
            {
                row = res.Rows[0];
            }

            Users users = ToModel(row);

            return users;
        }


        public int AddUser(string userNo, string userName, int userLevel, string password)
        {
            return SqlHelper.ExecuteNonQuery("Insert into Users(UserNo,UserName,UserLevel,Password) VALUES(@UserNo,@UserName,@UserLevel,@Password)", new SqlParameter("@UserNo", userNo), new SqlParameter("@UserName", userName),
                 new SqlParameter("@UserLevel", userLevel),
                 new SqlParameter("@Password", password));
        }

        public int UpdateUser(int id, string userNo, string userName, int? userLevel, string password)
        {
            DataTable dt = SqlHelper.ExecuteTable("select * from Users where Id=@Id", new SqlParameter("@Id", id));
            int rowCount = 0;
            if (dt.Rows.Count > 0)
            {
                DataRow row = dt.Rows[0];
                Users users = new Users();
                users.Id = (int)row["Id"];
                users.UserNo = userNo ?? row["UserNo"].ToString();
                users.UserName = userName ?? row["userName"].ToString();
                users.UserLevel = userLevel ?? (int)row["userLevel"];
                users.Password = password ?? row["password"].ToString();

                rowCount = SqlHelper.ExecuteNonQuery("Update users set UserNo=@UserNo,UserName=@UserName,UserLevel=@UserLevel,Password=@Password where Id=@Id", new SqlParameter("@UserNo", users.UserNo),
                        new SqlParameter("@UserName", users.UserName),
                        new SqlParameter("@UserLevel", users.UserLevel),
                        new SqlParameter("@Password", users.Password),
                        new SqlParameter("@Id", users.Id));
            }
            return rowCount;
        }


        public int RemoveUser(int Id)
        {
            return SqlHelper.ExecuteNonQuery("delete from Users where Id=@Id", new SqlParameter("@Id", Id));
        }

        private List<Users> ToModelList(DataTable table)
        {
            List<Users> userList = new List<Users>();
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i];
                Users user = ToModel(row);
                userList.Add(user);
            }
            return userList;
        }

        private Users ToModel(DataRow row)
        {
            Users users = new Users();
            users.Id = (int)row["Id"];
            users.UserNo = row["UserNo"].ToString();
            users.UserName = row["UserName"].ToString();
            users.UserLevel = (int)row["UserLevel"];
            users.Password = row["Password"].ToString();
            users.IsDelete = (bool)row["IsDelete"];
            return users;
        }
    }
}
